package org.study.clickhouse.api.util;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * 通过 JDBC 方式连接 ClickHouse。
 * 参考 https://help.aliyun.com/document_detail/167430.html
 *
 * @author Administrator
 * @date 2022-03-16
 */
public class ClickHouseMainTest1 {
    private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    private static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(DATE_FORMAT);

    public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
        String url = "jdbc:clickhouse://127.0.0.1:8123/default";
        String user = "default";
        String password = "root";
        Class.forName("com.clickhouse.jdbc.ClickHouseDriver");
        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement stmt = connection.createStatement()) {
            String dropLocalTableDDL = "DROP TABLE IF EXISTS test_table;";
            stmt.execute(dropLocalTableDDL);
            System.out.println("已刪除本地表……");

            String createLocalTableDDL = "create table test_table (" +
                    "id UInt32, " +
                    "dt_str String, " +
                    "dt_col DateTime" +
                    ") engine=MergeTree " +
                    "partition by toYYYYMM(dt_col) " +
                    "order by (id) " +
                    "primary key (id) " +
                    "sample by (id) " +
                    "settings index_granularity = 8192;";
            stmt.execute(createLocalTableDDL);
            System.out.println("已创建本地表……");

            /*String createTableDDL = "create table test_dist on cluster default as default.test_table engine=Distributed(default, default, test_table, rand());";
            stmt.execute(createTableDDL);
            System.out.println("已创建分布式表……");*/

            System.out.println("即将写入 10W 行记录……");
            long startTime = System.currentTimeMillis();
            // 分 10 个批次写入 10W 行记录，每个批次 1W 行
            for (int batch = 0; batch < 10; batch++) {
                StringBuilder sb = new StringBuilder();
                LocalDateTime now = LocalDateTime.now();
                String format = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").format(now);
                sb.append("insert into test_table values(" + (batch * 10000) + ", '" + format + "', '" + format + "')");
                for (int row = 1; row < 10000; row++) {
                    sb.append(", (" + (batch * 10000 + row) + ", '" + format + "', '" + format + "')");
                }
                stmt.execute(sb.toString());
            }
            long endTime = System.currentTimeMillis();
            System.out.println("写入 10W 行记录耗时" + (endTime - startTime) + "ms");

            Thread.sleep(2 * 1000);

            try (ResultSet rs = stmt.executeQuery("select count(id) from test_table");) {
                while (rs.next()) {
                    int count = rs.getInt(1);
                    System.out.println("count(id) 的结果：" + count);
                }
            }

            try (ResultSet rs = stmt.executeQuery("select id, dt_str, dt_col from test_table order by id desc limit 10")) {
                while (rs.next()) {
                    int id = rs.getInt(1);
                    String dateStr = rs.getString(2);
                    Timestamp time = rs.getTimestamp(3);
                    String defaultDate = SIMPLE_DATE_FORMAT.format(new Date(time.getTime()));
                    System.out.println("id: " + id + ", date_str:" + dateStr + ", date_col:" + defaultDate);
                }
            }
        }
    }
}
